Substitution Variables can be used to exchange values at run time for Parameters presented to the user before a Dashboard runs, for Members in the POV, for Global variables for Scenario or Time, or for Workflow variables for Time, Scenario or other Workflow Profile attributes.
Substitution Variables from Current POV
These settings will return the values from the user’s current POV for each Dimension.
By adding Desc to the end of many of these substitution variables, the Description for the Member will be returned instead of the Member name:
|PovEntityDesc| will return Akron instead of the Entity name 123.
POVCube and POVCubeDesc
POVEntity and POVEntityDesc
POVParent and POVParentDesc
POVConsolidation and POVConsolidationDesc
POVScenario and POVScenarioDesc
POVTime, POVTimeDesc and POVTimeShortDesc
POVView and POVViewDesc
POVAccount and POVAccountDesc
POVFlow and POVFlowDesc
POVOrigin and POVOriginDesc
POVIC and POVICDesc
POVUD1-UD8 and POVUD1-8Desc
NOTE: If there is an index out-of-range, the Cube View will display an error showing the data POV as invalid. When the Cube View is passed to the Pivot Grid control, it attempts to resolve the error column. It cannot, so in the case of the Time dimension it will instead display the default time member as "Name - Description". To resolve the issue in the Pivot Grid, you must first correct the issue in the source Cube View.
Substitution Periods are defined within a given Year. The number of sub periods are limited to the number of months or weeks within the given application year. For example, if you go prior or next within a given year, it will stop at the year boundary. If PovSubPeriodPrior14 in a given monthly app for a given year will not go past M1. PovSubPeriodNext will not go past M12. In a weekly app, PovSubPeriodPrior will not go past W1 and PovSubPeriodNext will not go past W53.
POVTimeDimProfile
This returns the Time Dimension Profile name associated with the user’s current Cube POV.
POVCurrency
This returns the actual currency for an Entity based on the Consolidation Dimension setting in the POV. For example, if Consolidation is set to Local for the Manchester Entity, GBP will return, which is its currency. If Consolidation is set to Translated and the Parent is set to US, USD will return.
POVTimePriorInYearN
This returns the prior (or more) time period if it is still in the current year.
POVTimePriorN
This returns the prior (or more) time period.
POVTimeNextInYearN
This returns the next (or more) time period if it is still in the current year.
POVTimeNextN
This returns the next (or more) time period.
POVYear
This returns the Year portion of a POV Time period.
POVYearPriorN
This returns the Year portion of a Time period for the previous year where N is the number of years.
POVYearNextN
This returns the Year portion of a Time period for the upcoming year where N is the number of years.
POVSubPeriod
This returns the Week, Month, Quarter, or Half Year portion of a Time period. If the POV is 2012M5, M5 is the SubPeriod.
POVSubPeriodPriorN
This returns the previous Week, Month, Quarter, or Half Year portion of a Time period.
POVSubPeriodNextN
This returns the next Week, Month, Quarter, or Half Year portion of a Time period.
POVSubPeriodNum
This returns the sub period’s number for the current POV, so if the POV is set to M3, the number 3 is returned.
POVTimeFirstInYear
This is the same as T#POVFirstInYear, T# W1, T#M1, T#Q1, or T#H1 based on the Input Frequency of the Scenario.
POVTimeLastInYear
This returns the last time period in the year based on the Input Frequency of the Scenario.
Workflow Substitution Variables
WF
This is used within the Point of View area for Scenario and Time: T#WF.
WFProfile
This provides the current Workflow Profile name.
WFProfileIndex
This provides a numeric value of the Workflow Profile in the hierarchy.
WFProfileLastDescendantIndex
This provides an index of bottom descendants in the Workflow Profile tree.
Workflow Profile Keys
These return a numeric internal ID for Workflow Profiles.
WFProfileKey
WFReviewProfileKeys
WFInputParentProfileKeys
WFImportProfileKeys
WFFormProfileKeys
WFJournalProfileKeys
WFScenario
This returns the Scenario in the Workflow View.
WFScenarioDesc
This returns a Description of the Scenario in the Workflow View.
WFScenarioID
This returns the Numeric ID of the Scenario in the Workflow View.
WFTime
This returns the Time period associated with current Workflow Unit.
WFTimeDesc and WFTimeShortDesc
This returns the Description (e.g. Feb 2011) or Short Description (e.g. Feb) for Workflow Time as defined in Time Profile.
WFTimeID
This returns the Numeric ID of the Workflow Unit Time period.
WFTimePriorInYearN
This returns the prior (or more) Workflow time period if it is still in the current year.
WFTimePriorN
This returns the prior (or more) time period.
WFTimeNextInYearN
This returns the next (or more) time period if it is still in the current year.
WFTimeNextN
This returns the next (or more) time period.
WFYear
This returns the year portion of the Workflow Unit.
WFYearPriorN
This returns the Year portion of a Time period for the previous year where N is the number of years.
WFYearNextN
This returns the Year portion of a Time period for the upcoming year where N is the number of years.
WFSubPeriod
This returns the Week, Month, Quarter, or Half Year portion of a Time period
WFSubPeriodPriorN
This returns the previous Week, Month, Quarter, or Half Year portion of a Time period.
WFSubPeriodNextN
This returns the next Week, Month, Quarter, or Half Year portion of a Time period.
WFSubPeriodNum
This returns the period’s number for the current Workflow Period, so if the Workflow is set to M3, the number 3 is returned.
WFTimeFirstInYear
This is the same as T#WFFirstInYear, T#W1, T#M1, T#Q1, or T#H1 based on the Input Frequency of the Scenario.
WFTimeLastInYear
This returns the last time period in the year based on the Input Frequency of the Scenario.
WFStartTime
This is the Workflow Start Time entry for this Scenario.
WFStartTimeDesc
This provides a Description of the Workflow Start Time entry for this Scenario.
WFStartTimeShortDesc
This provides a Short Description of the Workflow Start Time entry for this Scenario.
WFEndTime
This is the Workflow End Time entry for this Scenario.
WFEndTimeDesc
This provides the Description of the Workflow Start Time entry for this Scenario.
WFEndTimeShortDesc
This provides the Short description of the Workflow Start Time entry for this Scenario.
WFCubeRoot
This returns the very top level of the Workflow Profile hierarchy.
WFCube
This returns the Cube related to this Workflow Profile.
WFTimeDimProfile
This returns the Time Dimension Profile name associated with the user’s current Workflow POV.
WFEntityDim
The returns the Dimension in play for the Workflow Entities assigned to this Workflow Profile.
WFScenarioDim
This returns the Scenario Dimension for the Cube assigned to this Workflow Profile.
WFAccountDim
This returns the Account Dimension associated with the Cube for this Workflow Profile.
WFFlowDim
This returns the Flow Dimension for the Cube assigned to this Workflow Profile.
WFUD1Dim-WFUD8Dim
This returns the UD1 Dimension-UD8 Dimension for the Cube assigned to this Workflow Profile.
WFText1, WFText2, WFText3 and WFText4
This provides four optional text fields that can be populated and referenced from a Workflow Profile.
Global Substitution Variables
GlobalScenario
This is the Global Point of View Scenario from Application Properties.
GlobalTime
This is the Global Point of View Time period from Application Properties.
GlobalScenarioDesc
This provides the Descriptions for the Global Scenario.
GlobalTimeDesc and GlobalTimeShortDesc
This provides the Description (e.g. Feb 2011) or Short Description (e.g. Feb) for Global Time, as defined in the Time Profile.
Cube View Substitution Variables
These settings will return the values from the user’s current Cube View in the Point of View Slider for each Dimension. For the Dimensions not specified in the Cube View’s Point of View Slider, the Member will be represented by the user’s POV settings.
By adding Desc to the end of many of these substitution variables, the Description for the Member will be returned instead of the Member name:
|CVAccountDesc| will return Net Sales instead of 61000
|CVTimeDesc| will return Dec 2011 instead of 2011M12
|CVTimeShortDesc| will return Dec instead of 2011M12
The following Substitution Variables will return the Cube View Name, Cube Name, or Dimension Name associated with the specific Cube View as well as a Description.
CVName and CVDesc
CVCube and CVCubeDesc
CVTimeDimProfile
CVEntity and CVEntityDesc
CVParent and CVParentDesc
CVConsolidation and CVConsolidationDesc
CVScenario and CVScenarioDesc
CVTime, CVTimeDesc and CVTimeShortDesc
CVView and CVViewDesc
CVAccount and CVAccountDesc
CVFlow and CVFlowDesc
CVOrigin and CVOriginDesc
CVIC and CVICDesc
CVUD1-8 and CVUD1-8Desc
CVCurrency
This returns the actual currency for an Entity based on setting in the specific Cube View.
Additional Cube View Time Functions
See Substitution Variables from Current POV to see examples of these Time Functions.
CVTimePriorInYear
CVTimePrior
CVTimeNextInYear
CVTimeNext
CVYear
CVYearPrior
CVYearNext
CVSubPeriod
CVSubPeriodPrior
CVSubPeriodNext
CVSubPeriodNum
CVTimeFirstInYear
CVTimeLastInYear
General Substitution Variables
The script or variable will use pipe characters to include a pre-defined substitution variable, e.g. |UserName|. These can be used in Business Rules, Cube Views, and Dashboard headers.
Null
|Null| is empty text. |Null| is mostly used in Cube-level security and the Stage parser. I t can also be used within a comma-separated list of Parameter values when you need to set a value to be an empty string. However, |Null| is not a commonly used substitution variable.
Space
This is used to replace a string with nothing or use a space along with the Name function in a Member Filter. For example, in order to make a Column Header or Row Header blank, use T#POV:Name(“ ”) or T#POV:Name(|space|). Both will produce the same result.
UserName
This provides the current user name
UserText1-4
This allows the reference of the Text1 through Text4 properties related to a User account: |UserText3|.
AppName
This provides the application name
DateTimeForFileName
This returns the current date and time: 20131208_102540.
DateForFileName
This returns the current date: 20131208.
DateLong
This returns the current date: Sunday, December 08, 2013.
DateMMDDYYYY
This returns the current Date as Month, Day, Year: 12/08/2013.
DateDDMMYYYY
This returns the current Date as Day, Month, Year: 08/12/2013.
DateYYYYMMDD
This returns the current Date as Year, Month, Day: 2013/12/08.
DateTimeHHMMSS
This returns the current Date/Time as Hour, Minutes, Seconds: 11:00:19.
DateTimeForFileNameUTC
DateForFileNameUTC
DateLongUTC
DateMMDDYYYYUTC
DateDDMMYYYYUTC
DateYYYYMMDDUTC
DateTimeHHMMSSUTC
The version of these functions with the UTC suffix returns the same result, but in Coordinated Universal Time.
Member Filter Substitution Variables
In order to change how a Member appears in results, use the MF functions below. See the section on Changing the Time Label in Headers When Using These Time Functions under Time Functions for several examples of how these are used. These are used in Name and GetDataCell functions only.
MFTime
MFTimeDesc
MFTimeShortDesc
MFYear
MFSubPeriod
MFSubPeriodNum
The following Substitution Variables work with the XFMemberProperty function in order to retrieve any Dimension Member Name being used within a Member Filter. Add Desc to any of these Substitution Variables in order to display the Member Description instead of the Member Name. See XFMemberProperty under Commonly Used Member Filter Functions for more details on using these Substitution Variables.
|MFCube|
|MFEntity|
|MFParent|
|MFConsolidation|
|MFScenario|
|MFTime|
|MFView|
|MFAccount|
|MFView|
|MFFlow|
|MFOrigin|
|MFIC|
|MFUD1|-|MFUD8|
Loop1-4Variable
Loop1-4DisplayVariable
Loop1-4Index
Variable1-10
See Presenting Data With Books, Cube Views and Dashboards.
Custom Substitution Variables
For user-defined (i.e., custom) Substitution Variables, the user will enclose the variable name using both pipes and exclamation points: |!myVariable!|
This excludes the Member Filter Substitution Variables listed above.
A good use of this is for Parameters added to a Dashboard. A Parameter called SalesDashboardEntity might be created and prompt the user with a list of all Entities to choose from before filtering a Report. In the Cube View that drives this Report, limit the Cube View’s POV to the selected Parameter Entity by entering |!SalesDashboardEntity!|. When designing a Dashboard, |!SalesDashboardEntity!| can also be entered in the title of the Dashboard Component in order to be displayed at run time.
